How to Scale an AWS RDS MySQL Database Horizontally?

What is scalability

The scalability of an application is the measure of the number of client requests it can simultaneously handle. When a hardware resource runs out and can no longer handle requests, it is counted as the limit of scalability. When this limit of the resource is reached, the application can no longer handle additional requests. To efficiently handle additional requests, administrators should scale the infrastructure by adding more resources such as RAM, CPU, storage, network devices, etc. Horizontal and vertical scaling are the two methods implemented by administrators for capacity planning.

What is Horizontal Scaling?

Horizontal scaling is an approach of adding more devices to the infrastructure to increase the capacity and efficiently handle increasing traffic demands. As the name says, horizontal scaling is about expanding the capacity horizontally by adding extra servers. The load and processing power are shared among multiple servers within a system using a load balancer. It is also called scaling out.

What is Vertical Scaling?

Vertical scaling is a type of scalability wherein more computing and processing power is added to a machine to increase its performance. Also called scale-up, vertical scaling allows you to increase the machine’s capacity while maintaining resources within the same logical unit. The processor, memory, storage, and network capacity are increased in this approach.

Scalability Issues of RDBMS (Specific to MySQL)

As we discussed earlier, vertical scalability has some hardware upper limits. Vertical scaling also requires some downtime. We cannot afford both in the database world. So we need to look into horizontal scalability options. In a database world, horizontal scaling is usually based on the partitioning of data (each partition only contains part of the data). Partitioning requires more effort and thought process in the development and design phase. That is a separate process and we’re not discussing that here.

Scaling MySQL Using Read Replicas

The read replica feature allows you to replicate data from MySQL server to one or more read-only servers. Replicas are updated asynchronously using the MySQL engine’s native binary log file position-based replication technology.

In this case, we will create a Master-Slave architecture and route all the write queries on the Master instance and all the read queries on the slave instance which are replicated from the Master. We can have multiple Slave instances running at one and scale our read operations horizontally. But the Master can only be scale Vertically. In most of the cases databases are read heavy, so this approach will work in most of the use cases.

Step 1: Application Development Considerations

While developing the application, we should follow the CQRS design pattern. CQRS stands for Command and Query Responsibility Segregation, a pattern that separates read and update operations for a data store. Implementing CQRS in your application can maximize its performance, scalability, and security. The flexibility created by migrating to CQRS allows a system to better evolve over time and prevents update commands from causing merge conflicts at the domain level. In short our application will have two connection strings, one for read operations and the another one for update operations.

As we have a single Master write node we can use that connection string for update operations and we will have multiple read nodes(slaves), so we need to setup a load balancer for that which will equally distribute the load among multiple read nodes.

Step 2: Setup Load Balancer for Read Only Nodes

We can set up Amazon Route 53 weighted record sets to distribute requests across your read replicas. Within a Route 53 hosted zone, create individual record sets for each DNS endpoint associated with your read replicas. Then, give them the same weight, and direct requests to the sub domain/endpoint of the record set.

How to Create Read Replicas

Assuming that you already have a MySQL RDS in your AWS account. Follow the below steps to create a read replica, and repeat the steps to create multiple replicas if required. In order to evaluate the load balancing feature, we should create at least 2 replicas.

• Type rds in AWS Console search box and select RDS
• Select Databases from the left panel
• Select the database you want to create read replica on
• Click on the Actions menu and select Create read replica as shown in the below screenshot.

• Then select the Db Instance class
• Select Publicly Accessible to Yes
• Select the VPC Security Groups(You can select the same security group of your master node)
• Enter the Db Instance Indentifier and then click ‘Create Read Replica`

Read replica will be created in a few minutes. Repeat the above steps to create one more node.

Create DNS Based Load Balancer

To create a DNS based load balancer, you have to set up a hosted zone in Route 53. Follow the below steps to create a hosted zone and record set.

• Type route 53 in AWS Console search box and select Route 53 from the result.
• Click Create Hosted Zone

• Enter the Domain name, Description is optional
• Select the Public hosted zone in Type option
• Click Create hosted zone

• Now we need to create Records in the newly created hosted zone
• Select Create Record
• Enter a subdomain name in the Name field
• Select CNAME as Type
• For Value enter the endpoint DNS name of the first read replica
• For TTL value, set a value that is appropriate for your needs
• For Routing Policy, choose Weighted
• In the Weight field, enter a value. Be sure to use the same value for each replica’s record set
• Provide an Id for the Record set
• Repeat the steps to create records for all the replicas. Keep the same name (subdomain) for all the records.

Now your records would look like the below screenshot:

Update the NS records Entries in the Custom Name Server

Now copy the all four NS record values. You have to go to your Domain Registrar’s portal (godaddy, google domains etc.) and update the custom name server values there. In case of Google Domains, that will look like below. The changes may take some time to reflect.

Once your NS records are properly updated, you will be able to use the newly created subdomain as your read only database host name. You can use the same credentials of your master database to access the read only load balanced instances

Now you can configure your Master hostname for CUD operations and load balanced hostname for Read operations.